﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;

namespace Peter.DataAccess.Sqlite
{
   /// <summary>
   /// An adapter for reading and writing to a sqlite data base.
   /// </summary>
   public abstract class SqliteAdapter
   {
      private SQLiteTransaction m_Transaction;
      private readonly string m_PathToDatabase;

      /// <summary>
      /// Initializes a new sqlite adapter.
      /// </summary>
      /// <param name="pathToDatabase">Path to database to use.</param>
      protected SqliteAdapter (string pathToDatabase)
      {
         this.m_PathToDatabase = pathToDatabase;
      }

      /// <summary>
      /// Gets or Sets if the database needs to be saved.
      /// </summary>
      public bool NeedsSaving { get; set; }

      /// <summary>
      /// Gets the sqlite connection.
      /// </summary>
      protected SQLiteConnection SqliteConnection { get; private set; }

      /// <summary>
      /// Gets the connection string to a sqlite database.
      /// </summary>
      protected string ConnString
      {
         get { return string.Format ("Data Source={0};Version=3;", this.m_PathToDatabase); }
      }

      /// <summary>
      /// Creates a connection to the given database.
      /// </summary>
      public void Connect ()
      {
         this.SqliteConnection = new SQLiteConnection (this.ConnString);
         this.SqliteConnection.Open ();
      }

      /// <summary>
      /// Saves the database data.
      /// </summary>
      public void Save ()
      {
         if (this.m_Transaction != null)
         {
            this.m_Transaction.Commit ();
            this.m_Transaction.Dispose ();
            this.m_Transaction = null;
         }
         this.NeedsSaving = false;
      }

      /// <summary>
      /// Creates a new table in the database.
      /// </summary>
      /// <param name="name">Name of table to add.</param>
      /// <param name="columns">List of columns to add.</param>
      /// <returns>Error message if any.</returns>
      protected string CreateTable (string name, IList<SqliteColumn> columns)
      {
         try
         {
            if (this.m_Transaction == null)
               this.m_Transaction = this.SqliteConnection.BeginTransaction ();
            var command = this.SqliteConnection.CreateCommand ();
            string query = string.Format ("CREATE TABLE {0} (", name);
            query = columns.Aggregate (query, (current, column) => current + (column.Name + " " + column.DataType + ", "));
            query = query.Replace (SqliteColumnType.IntegerPrimaryKey.ToString (), "INTEGER PRIMARY KEY");
            query = query.Trim ().TrimEnd (',') + ");";
            command.CommandText = query;
            command.ExecuteNonQuery ();
            this.NeedsSaving = true;
         }
         catch (SQLiteException ex)
         {
            return ex.Message;
         }

         return string.Empty;
      }
   }
}
